﻿using Dapper;
using Entity;
using IRepository;
using Microsoft.Data.SqlClient;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Repository
{
    public class TypeOfGoodsRepository : ITypeOfGoodsRepository
    {
        private readonly IDbConnection _dbConnection;

        public TypeOfGoodsRepository(IDbConnection dbConnection)
        {
            _dbConnection = dbConnection;
        }


        public async Task<List<WMSBasicTypeOfGoods>> GetAllGoodsTypesAsync(string goodsType = null, string status = null, string @operator = null, DateTime? startTime = null, DateTime? endTime = null)
        {
            var sqlBuilder = new StringBuilder();
            sqlBuilder.Append("SELECT product_type_id, parent_category_id, product_type_code, product_type_name, sorting, note, create_by, create_time, update_by, update_time, is_delete, status ");
            sqlBuilder.Append("FROM dbo.wms_basic_type_of_goods ");
            sqlBuilder.Append("WHERE is_delete = '0' "); // 仅查询“未逻辑删除”的有效数据

            var parameters = new DynamicParameters();

            // 货品类型（名称/编号模糊查询）
            if (!string.IsNullOrEmpty(goodsType))
            {
                sqlBuilder.Append("AND (product_type_name LIKE @goodsType OR product_type_code LIKE @goodsType) ");
                parameters.Add("goodsType", $"%{goodsType}%");
            }

            // 状态（启用/停用）：假设数据库中 status=1 为启用，status=2 为停用
            if (!string.IsNullOrEmpty(status))
            {
                sqlBuilder.Append("AND status = @status ");
                parameters.Add("status", status == "启用" ? 1 : 2);
            }

            // 操作人（模糊查询）
            if (!string.IsNullOrEmpty(@operator))
            {
                sqlBuilder.Append("AND create_by LIKE @operator ");
                parameters.Add("operator", $"%{@operator}%");
            }

            // 操作时间范围
            if (startTime.HasValue)
            {
                sqlBuilder.Append("AND create_time >= @startTime ");
                parameters.Add("startTime", startTime.Value);
            }
            if (endTime.HasValue)
            {
                sqlBuilder.Append("AND create_time <= @endTime ");
                parameters.Add("endTime", endTime.Value);
            }

            sqlBuilder.Append("ORDER BY sorting");

            var result = (await _dbConnection.QueryAsync<WMSBasicTypeOfGoods>(sqlBuilder.ToString(), parameters)).ToList();
            Console.WriteLine($"查询到的货品类型总数：{result.Count}");
            return result;
        }

        public async Task<List<WMSBasicTypeOfGoods>> GetSelectTypesAsync()
        {
            string sql = "select product_type_id,parent_category_id," +
                "product_type_name,product_type_code,sorting,note,create_by" +
                " from dbo.wms_basic_type_of_goods where is_delete = '0' ";
            return (await _dbConnection.QueryAsync<WMSBasicTypeOfGoods>(sql)).ToList();
        }

        /// <summary>
        /// 新增类型
        /// </summary>  
        /// <param name="model"></param>
        /// <returns></returns>
        /// <exception cref="NotImplementedException"></exception>
        public async Task AddTypesAsync(AddWMSBasicTypeOfGoods model)
        {
            string sql = @"insert into wms_basic_type_of_goods
                           (parent_category_id,product_type_code,product_type_name,sorting,note,create_by,create_time,status)
                            values (@parent_category_id,@product_type_code,@product_type_name,@sorting,@note,@create_by,@create_time,@status)";

            await _dbConnection.ExecuteAsync(sql,model);
        }

        public async Task DeleteTypesAsync(string product_type_code)
        {
            string sql = @"update wms_basic_type_of_goods set is_delete = '1'
                           where product_type_code = @product_type_code";
            await _dbConnection.ExecuteAsync(sql,new { product_type_code } );
        }

        public async Task UpdateTypesAsync(AddWMSBasicTypeOfGoods model)
        {
            
            string sql = @"update wms_basic_type_of_goods set parent_category_id = @parent_category_id,
                       product_type_name = @product_type_name,sorting = @sorting,note = @note,status = @status,
                       update_by = @update_by,update_time = @update_time 
                       where product_type_code = @product_type_code";
            await _dbConnection.ExecuteAsync(sql, model);
            
                
        }

        public async Task<AddWMSBasicTypeOfGoods> SelectTypeAsync(string product_type_code)
        {
            string sql = @"select product_type_id,parent_category_id,product_type_code,
                           product_type_name,sorting,note,status from wms_basic_type_of_goods 
                           where product_type_code = @product_type_code";
            return (await _dbConnection.QueryAsync<AddWMSBasicTypeOfGoods>(sql, new { product_type_code })).FirstOrDefault();
        }

        public async Task<List<WMSBasicTypeOfGoods>> GetAllGoodsTypesAsync()
        {
            string sql = "SELECT product_type_id, parent_category_id, product_type_code, product_type_name, sorting, note, create_by, create_time, update_by, update_time, is_delete " +
                         "FROM dbo.wms_basic_type_of_goods " +
                         "WHERE is_delete = '0' " +
                         "ORDER BY sorting";
            var result = (await _dbConnection.QueryAsync<WMSBasicTypeOfGoods>(sql)).ToList();
            // 打印总数量（应包含一级和二级类型，如你提供的数据有12条）
            Console.WriteLine($"查询到的货品类型总数：{result.Count}");
            return result;
        }

        /// <summary>
        /// 根据类型ID获取类型名称
        /// </summary>
        public async Task<string> GetTypeNameByIdAsync(int typeId)
        {
            if (typeId <= 0)
                return string.Empty;

            string sql = "SELECT product_type_name " +
                         "FROM dbo.wms_basic_type_of_goods " +
                         "WHERE product_type_id = @TypeId AND is_delete = '0'";

            // 查询单值，不存在则返回null
            var typeName = await _dbConnection.QueryFirstOrDefaultAsync<string>(
                sql,
                new { TypeId = typeId } // 参数化查询，防止SQL注入
            );

            return typeName ?? string.Empty;
        }
        /// <summary>
        /// 批量根据类型ID获取类型名称（新增实现）
        /// </summary>
        public async Task<Dictionary<int, string>> GetTypeNamesByIdsAsync(List<int> typeIds)
        {
            if (typeIds == null || typeIds.Count == 0)
                return new Dictionary<int, string>();

            // SQL查询：批量查询多个ID对应的名称（避免循环查询）
            string sql = @"
                SELECT product_type_id, product_type_name 
                FROM dbo.wms_basic_type_of_goods 
                WHERE product_type_id IN @TypeIds AND is_delete = '0'";

            // 执行查询并转换为字典（键：ID，值：名称）
            var result = await _dbConnection.QueryAsync<(int product_type_id, string product_type_name)>(
                sql,
                new { TypeIds = typeIds } // 参数化查询，防止SQL注入
            );

            return result.ToDictionary(
                item => item.product_type_id,
                item => item.product_type_name ?? "未知类型"
            );
        }
    }
}
